<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> 
<html>
<head>
<title>Transactions</title>
<link rel="stylesheet" href="/cfg/format.css" type="text/css">
<meta http-equiv="content-type" content="text/html; charset=utf-8">
<meta name="keywords" content="Ruby, SQLite, transactions, database, sqlite3">
<meta name="description" content="In this part of the SQLite Ruby tutorial, 
we work with database transactions and the autocommit mode.">
<meta name="language" content="en">
<meta name="author" content="Jan Bodnar">
<meta name="distribution" content="global">

<script type="text/javascript" src="/lib/jquery.js"></script>
<script type="text/javascript" src="/lib/common.js"></script>

</head>

<body>

<div class="container">

<div id="wide_ad" class="ltow"> 
<script type="text/javascript"><!--
google_ad_client = "pub-9706709751191532";
/* 160x600, August 2011 */
google_ad_slot = "2484182563";
google_ad_width = 160;
google_ad_height = 600;
//-->
</script> 
<script type="text/javascript"
src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> 
</script> 
</div> 

<div class="content">


<a href="/" title="Home">Home</a>&nbsp;
<a href="..">Contents</a>


<h1>Transactions</h1>

<p>
In this chapter, we will work with transactions. First, we provide some
basic definitions. Then we present Ruby scripts that show, how to work
with transactions in Ruby sqlite3 module. We will also talk about the 
autocommit mode, which is essential to understand when dealing with 
transactions. 
</p>

<div class="center">
<script type="text/javascript"><!--
google_ad_client = "ca-pub-9706709751191532";
/* top_horizontal */
google_ad_slot = "3327173442";
google_ad_width = 468;
google_ad_height = 60;
//-->
</script>
<script type="text/javascript"
src="http://pagead2.googlesyndication.com/pagead/show_ads.js">
</script>
</div>


<h2>Definitions</h2>

<p>
A <b>transaction</b> is an atomic unit of database operations against 
the data in one or more databases. The effects of all the SQL 
statements in a transaction can be either all committed 
to the database or all rolled back. In the <b>autocommit mode</b>
the changes are immediately effective. To work with transactions we
start a transaction with the <code>transaction</code> method. The 
transaction is ended with either the <code>commit</code> or 
<code>rollback</code> methods. 
</p>

<p>
The database connection is in the autocommit mode by default. Note
that the default mode depends on the driver. In the SQLite Python
driver, the autocommit is turned off by default. 
</p>

<p>
In SQLite, any command other than the SELECT will start an implicit 
transaction. Also, within a transaction a command like CREATE TABLE ..., 
VACUUM, PRAGMA, will commit previous changes before executing. Manual 
transactions are started with the BEGIN TRANSACTION statement and 
finished with the COMMIT OR ROLLBACK statements.
</p>

<p>
SQLite supports three non-standard transaction levels. DEFERRED, 
IMMEDIATE and EXCLUSIVE. 
</p>


<h2>Examples</h2>

<p>
Now we will have some scripts that work with transactions and the
autocommit mode. 
</p>

<pre class="code">
#!/usr/bin/ruby

require 'sqlite3'

begin
    
    db = SQLite3::Database.open "test.db"
    
    db.execute "DROP TABLE IF EXISTS Friends"
    db.execute "CREATE TABLE Friends(Id INTEGER PRIMARY KEY, Name TEXT)"
    db.execute "INSERT INTO Friends(Name) VALUES ('Tom')"
    db.execute "INSERT INTO Friends(Name) VALUES ('Rebecca')"
    db.execute "INSERT INTO Friends(Name) VALUES ('Jim')"
    db.execute "INSERT INTO Friends(Name) VALUES ('Robert')"
    db.execute "INSERT INTO Friends(Name) VALUES ('Julian')"
    
rescue SQLite3::Exception => e 
    
    puts "Exception occured"
    puts e
    
ensure
    db.close if db
end
</pre>

<p>
We create a Friends table and fill it with data. We do not explicitly start
a transaction, nor we call commit or rollback methods. Yet the data is
written to the table. It is because by default, we work in the autocommit mode.
In this mode each SQL statement is immediately effective.
</p>

<pre class="explanation">
db.execute "DROP TABLE IF EXISTS Friends"
db.execute "CREATE TABLE Friends(Id INTEGER PRIMARY KEY, Name TEXT)"
</pre>

<p>
We drop the Friends table if it already exists. Then we create the
table with the CREATE TABLE statement. 
</p>

<pre class="explanation">
db.execute "INSERT INTO Friends(Name) VALUES ('Tom')"
db.execute "INSERT INTO Friends(Name) VALUES ('Rebecca')"
...
</pre>

<p>
We insert data. 
</p>

<pre>
$ ./autocommit.rb
$ sqlite3 test.db 
SQLite version 3.7.7 2011-06-23 19:49:22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> SELECT * FROM Friends;
1|Tom
2|Rebecca
3|Jim
4|Robert
5|Julian
</pre>

<p>
We execute the script and check the table with the sqlite3 command
line tool. The Friends table is succesfully created.
</p>

<hr class="btm">

<p>
In the second example we will start a transaction with
the <code>transaction</code> method.
</p>

<pre class="code">
#!/usr/bin/ruby

require 'sqlite3'

begin
    
    db = SQLite3::Database.open "test.db"

    db.transaction
    db.execute "DROP TABLE IF EXISTS Friends"
    db.execute "CREATE TABLE Friends(Id INTEGER PRIMARY KEY, Name TEXT)"
    db.execute "INSERT INTO Friends(Name) VALUES ('Tom')"
    db.execute "INSERT INTO Friends(Name) VALUES ('Rebecca')"
    db.execute "INSERT INTO Friends(Name) VALUES ('Jim')"
    db.execute "INSERT INTO Friends(Name) VALUES ('Robert')"
    db.execute "INSERT INTO Friends(Name) VALUES ('Julian')"
    db.execute "INSERT INTO Friends(Name) VALUES ('Michael')"
    db.commit
    
rescue SQLite3::Exception => e 
    
    puts "Exception occured"
    puts e
    db.rollback
    
ensure
    db.close if db
end
</pre>

<p>
We recreate the Friends table. After the <code>transaction</code> method call,
every statement is within a transaction until we call the <code>commit</code> method. 
We either save all changes or save nothing. This is the basic idea behind
transactions.
</p>

<pre class="explanation">
db.transaction
</pre>

<p>
The <code>transaction</code> method begins a new transaction. The method takes
an optional mode parameter, where we can specify the transaction level. The
default level is DEFERRED. 
</p>

<pre class="explanation">
db.commit
</pre>

<p>
The changes are written to the database. If we commented the line, the changes
would not be saved. 
</p>

<pre class="explanation">
db.rollback
</pre>

<p>
In case of an error, we rollback the changes. 
</p>

<pre>
sqlite> SELECT * FROM Friends;
1|Tom
2|Rebecca
3|Jim
4|Robert
5|Julian
6|Michael
</pre>

<p>
We verify with the sqlite3 command line tool that the changes were written.
</p>


<hr class="btm">

<p>
When there is an error in the transaction, the transaction is rolled back
an no changes are committed to the database. 
</p>

<pre class="code">
#!/usr/bin/ruby

require 'sqlite3'

begin
    
    db = SQLite3::Database.open "test.db"

    db.transaction
    db.execute "UPDATE Friends SET Name='Thomas' WHERE Id=1"
    db.execute "UPDATE Friend SET Name='Bob' WHERE Id=4"
    db.commit
    
rescue SQLite3::Exception => e 
    
    puts "Exception occured"
    puts e
    db.rollback
    
ensure
    db.close if db
end
</pre>

<p>
In the code example we want to change two names. There are two 
statements which form a transaction. There is an error in the 
second SQL statement. Therefore the transaction is rolled back.
</p>

<pre class="explanation">
db.execute "UPDATE Friend SET Name='Bob' WHERE Id=4"
</pre>

<p>
The name of the table is incorrect. There is no Friend table in
the database. 
</p>

<pre>
$ ./rollingback.rb
Exception occured
no such table: Friend
</pre>

<p>
Running the example will display this error message. The transaction is 
rolled back. 
</p>

<pre>
sqlite> SELECT * FROM Friends;
1|Tom
2|Rebecca
3|Jim
4|Robert
5|Julian
</pre>

<p>
No changes took place in the Friends table. Even if the first UPDATE statement
was correct. 
</p>

<hr class="btm">

<p>
We will again try to change two rows; this time in the autocommit mode.
</p>

<pre class="code">
#!/usr/bin/ruby

require 'sqlite3'

begin
    
    db = SQLite3::Database.new "test.db"
    
    db.execute "UPDATE Friends SET Name='Thomas' WHERE Id=1"
    db.execute "UPDATE Friend SET Name='Bob' WHERE Id=4"
    
rescue SQLite3::Exception => e 
    
    puts "Exception occured"
    puts e
    
ensure
    db.close if db
end
</pre>

<p>
We try to update two names in the Friends table. Tom to Thomas and
Robert to Bob. 
</p>

<pre class="explanation">
db.execute "UPDATE Friends SET Name='Thomas' WHERE Id=1"
db.execute "UPDATE Friend SET Name='Bob' WHERE Id=4"
</pre>

<p>
The second of the UPDATE statements is incorrect. 
</p>


<pre>
$ ./autocommit2.rb
Exception occured
no such table: Friend
</pre>

<p>
We receive the same error message as in the previous example.
</p>

<pre>
sqlite> SELECT * FROM Friends;
1|Thomas
2|Rebecca
3|Jim
4|Robert
5|Julian
</pre>

<p>
However this time, the first UPDATE statement was saved. The second one
was not. 
</p>

<p>
In this part of the SQLite Ruby tutorial, we have worked with transactions.
</p>


<div class="botNav, center">
<span class="botNavItem"><a href="/">Home</a></span> ‡ <span class="botNavItem"><a href="..">Contents</a></span> ‡ 
<span class="botNavItem"><a href="#">Top of Page</a></span>
</div>


<div class="footer">
<div class="signature">
<a href="/">ZetCode</a> last modified April 11, 2012  <span class="copyright">&copy; 2007 - 2013 Jan Bodnar</span>
</div>
</div>

</div> <!-- content -->

</div> <!-- container -->

</body>
</html>
